package com.xmh.ssm.utils.automaticGeneration;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;
import com.xmh.SpringBootDemo1Application;
import com.xmh.ssm.utils.FileUtil;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.stereotype.Component;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static java.lang.String.*;

/**
 * 代码自动生成
 *
 * @author xmh
 */
@RunWith(SpringRunner.class)
@Component
@SpringBootTest(classes = SpringBootDemo1Application.class)
public class AutomaticCodeGenerationBySBM {


    private final String vo_Path = "K:\\word_IDEA\\Spring-Boot-Demo\\src\\main\\java\\com\\xmh\\demo\\entity\\";

    private final String controller_Path = "K:\\word_IDEA\\Spring-Boot-Demo\\src\\main\\java\\com\\xmh\\demo\\controller\\";

    private final String ui_controller_Path = "K:\\word_IDEA\\Spring-Boot-Demo\\src\\main\\java\\com\\xmh\\demo\\controller\\";

    private final String service_Path = "K:\\word_IDEA\\Spring-Boot-Demo\\src\\main\\java\\com\\xmh\\demo\\service\\";

    private final String service_consumer_Path = "D:\\Word\\SmartCampus\\SmartCampus_financeUi\\src\\main\\java\\com\\tengyun\\service\\consumer\\";

    private final String dao_Path = "K:\\word_IDEA\\Spring-Boot-Demo\\src\\main\\java\\com\\xmh\\demo\\dao\\";

    private final String xml_Path = "K:\\word_IDEA\\Spring-Boot-Demo\\src\\main\\resources\\mapper\\";

    private final String[] table_Name_List = {  };

    @Autowired
    private DruidDataSource druidDataSource;

    private final String controle_Code = FileUtil
            .getFileStr(this.getClass().getResource("/model/SBM/controleModel.model").getPath());

    private final String service_Impl_Code = FileUtil
            .getFileStr(this.getClass().getResource("/model/SBM/serviceImplementsModel.model").getPath());

    private final String service_Inte_Code = FileUtil
            .getFileStr(this.getClass().getResource("/model/SBM/serviceInterfaceModel.model").getPath());

    private final String dao_Inte_Code = FileUtil
            .getFileStr(this.getClass().getResource("/model/SBM/daoInterface.model").getPath());

    private final String service_consumer_Code = FileUtil
            .getFileStr(this.getClass().getResource("/model/SBM/serviceConsumer.model").getPath());

    private final String ui_controle_Code = FileUtil
            .getFileStr(this.getClass().getResource("/model/SBM/uIcontroleModel.model").getPath());

    @Test
    public void create() throws Exception {
        List<Object> tableList = getTableMapList();

        createVo(tableList);
        createXml(tableList);
        createMode(tableList, dao_Inte_Code, dao_Path, "DAO");
        createMode(tableList, controle_Code, controller_Path, "Controller");
        createMode(tableList, service_Inte_Code, service_Path, "Service");
        createMode(tableList, service_Impl_Code, service_Path, "ServiceImpl");

//		createMode(tableList, ui_controle_Code, ui_controller_Path, "Controller");
//		createModeApp(tableList, service_consumer_Code, service_consumer_Path, "Service");
    }

    private List<Object> getTableMapList() throws SQLException {
        // 获得元数据
        DruidPooledConnection conn = druidDataSource.getConnection();

        DatabaseMetaData metaData = conn.getMetaData();
        // 先判断有没有指定获取哪个表格，没有的话就获取全部
        Map tableNameRemarks = getTablesMap(metaData);

        List result = new ArrayList();

        //查询每个表的字段
        for (Object obj : tableNameRemarks.keySet()) {
            String talbeName = (String) obj;
            // 先用SQL查询出所有字段，防止getColumns方法重复
            Map column = getColumn(conn, talbeName);

            List list = new ArrayList();

            ResultSet columns = metaData.getColumns(null, null, talbeName, "%");

            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");

                Integer value = (Integer) column.get(columnName);
                if (value == null || value == 2) {
                    continue;
                } else {
                    column.put(columnName, 2);
                }

                Map<String, String> columnNameMap = new HashMap<>(); // 保存字段名
                columnNameMap.put("type", columns.getString("TYPE_NAME"));
                columnNameMap.put("columnName", columnName);
                columnNameMap.put("remarks", columns.getString("REMARKS"));
                list.add(columnNameMap);
            }

            Map map = new HashMap();
            map.put("tableName", talbeName);
            map.put("columnList", list);
            map.put("remarks", tableNameRemarks.get(talbeName));
            result.add(map);
        }
        return result;
    }

    private Map getTablesMap(DatabaseMetaData metaData) throws SQLException {
        if (table_Name_List.length == 0) {
            // 从元数据中获取到所有的表名极其表格注释
            return getTableNameRemarks(metaData);
        } else {
            Map tableNameRemarks = new HashMap();
            for (String tableName : table_Name_List) {
                tableNameRemarks.put(tableName, "");
            }
            return tableNameRemarks;
        }
    }

    /**
     * 从元数据中获取到所有的表名极其表格注释
     *
     * @param metaData 元数据
     * @return 结果map
     * @throws SQLException sql 异常
     */
    private Map getTableNameRemarks(DatabaseMetaData metaData) throws SQLException {
        //从元数据中获取到所有的表名
        ResultSet rs = metaData.getTables(null, null, null, new String[]{"TABLE"});

        String dataBaseName = StringUtils.substringBetween(metaData.getURL(), ":3306/", "?");

        //存放所有表名
        Map tableNameRemarks = new HashMap();
        while (rs.next()) {
            String table_cat = rs.getString("TABLE_CAT");
/*            System.out.println("表名: " + rs.getString("TABLE_NAME"));
            System.out.println("表类型: " + rs.getString("TABLE_TYPE"));
            System.out.println("表所属数据库: " + table_cat);
            System.out.println("表所属用户名: " + rs.getString("TABLE_SCHEM"));
            System.out.println("表备注: " + rs.getString("REMARKS"));*/
            if (dataBaseName.equals(table_cat)) {
                tableNameRemarks.put(rs.getString("TABLE_NAME"), rs.getString("REMARKS"));
            }
        }
        return tableNameRemarks;
    }

    private Map getColumn(DruidPooledConnection conn, String tableName) throws SQLException {
        String sql = "select * from " + tableName;

        ResultSetMetaData meta = conn.prepareStatement(sql).executeQuery().getMetaData();

        Map map = new HashMap();
        for (int i = 1; i <= meta.getColumnCount(); i++) {
            map.put(meta.getColumnName(i), 1);
//            map.put(meta.getColumnName(i), meta.getColumnTypeName(i));
        }
        return map;
    }

    /**
     * 根据模板文件创建对应的文件以及对应路径
     *
     * @param tableList
     * @param modeCode
     * @param path
     * @param nameSuffix
     */
    @SuppressWarnings("unchecked")
    private void createMode(List<Object> tableList, String modeCode, String path, String nameSuffix) {
        for (Object object : tableList) {
            Map<String, Object> tableMap = (Map<String, Object>) object;
            // 转为驼峰
            String voName = nameTransformation((String) tableMap.get("tableName"));
            // 首字母大写
            String className = titleCase(voName);

            String fileName = className + nameSuffix;

            String filePath = path + (fileName.substring(fileName.length() - 4).equals("Impl") ? "\\impl\\" : "");

            String code = extracted(voName, className, modeCode);

            outFile(code, filePath, fileName, "java");
        }
    }

    @SuppressWarnings("unchecked")
    private void createModeApp(List<Object> tableList, String modeCode, String path, String nameSuffix) {
        for (Object object : tableList) {
            Map<String, Object> tableMap = (Map<String, Object>) object;
            // 转为驼峰
            String voName = nameTransformation((String) tableMap.get("tableName"));
            // 首字母大写
            String className = titleCase(voName);

            String fileName = "FinanceService";

            String code = extracted(voName, className, modeCode);

            try {
                File file = new File(format("%s//%s.java", path, fileName));

                if (!file.exists()) {
                    new File(path).mkdirs();
                    file.createNewFile();
                }

                String txtFile = "";

                BufferedReader bufferedReader = new BufferedReader(new FileReader(file));

                String line = bufferedReader.readLine();

                while (line != null) {
                    txtFile += line + "\n";
                    line = bufferedReader.readLine();
                }

                bufferedReader.close();

                StringBuilder sBuffer = new StringBuilder(txtFile.replace("}", ""));

                String string = sBuffer.append(code).toString();

                OutputStreamWriter write = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");
                BufferedWriter writer = new BufferedWriter(write);
                writer.write(string);
                writer.flush();
                writer.close();
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }


    /**
     * @param voName    1
     * @param className 2
     * @param code      3
     * @return 4
     */
    private String extracted(String voName, String className, String code) {
        String nowStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());

        String replace = code.replace("______y______", voName);
        replace = replace.replace("_____X______", className);
        replace = replace.replace("____Date____", nowStr);
        replace = replace.replace("___EPath___", getPackagePath(vo_Path));
        replace = replace.replace("___SCPath___", getPackagePath(service_consumer_Path));
        replace = replace.replace("___SPath___", getPackagePath(service_Path));
        replace = replace.replace("___DPath___", getPackagePath(dao_Path));
        replace = replace.replace("___CPath___", getPackagePath(controller_Path));

        return replace;
    }

    /**
     * 根据文件相对路径获取包路径
     *
     * @param path
     * @return
     */
    private String getPackagePath(String path) {
        String str = path.split("src")[1];
        String substring = str.substring(1, str.length() - 1);

        if (!substring.startsWith("com")) {
            String string = path.split("java")[1];
            substring = string.substring(1, string.length() - 1);
        }

        String packPach = substring.replace("\\", ".");
        return packPach;
    }

    /**
     * 创建xml文件
     *
     * @param tableNameMaplist
     */
    @SuppressWarnings("unchecked")
    private void createXml(List<Object> tableNameMaplist) {
        for (Object object : tableNameMaplist) {
            Map<String, Object> tableMap = (Map<String, Object>) object;

            String tableName = (String) tableMap.get("tableName");
            // 转为驼峰
            String voName = nameTransformation(tableName);
            // 首字母大写
            String className = titleCase(voName);

            StringBuilder asColumn = new StringBuilder();
            StringBuilder ifLabel = new StringBuilder();
            StringBuilder addColumnUp = new StringBuilder();
            StringBuilder addColumnDown = new StringBuilder();
            StringBuilder updateSetLabelSb = new StringBuilder();

            List<Object> list = (List<Object>) tableMap.get("columnList");
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> columnMap = (Map<String, Object>) list.get(i);

                String columnName = (String) columnMap.get("columnName");

                String type = (String) columnMap.get("type");

                // 字段名转为驼峰
                String cName = nameTransformation(columnName);

                if (i == list.size() - 1) {
                    asColumn.append(format("\t\t\t%s AS %s\n", columnName, cName));
                    addColumnUp.append(format("\t\t\t%s)\n", columnName));
                    addColumnUp.append("		VALUES ( \n");
                    addColumnDown.append(format("\t\t\t#{%s})\n", cName));
                } else {
                    asColumn.append(format("\t\t\t%s AS %s,\n", columnName, cName));
                    addColumnUp.append(format("\t\t\t%s,\n", columnName));
                    addColumnDown.append(format("\t\t\t#{%s},\n", cName));
                }

                String ifStartBelan = null;
                if ("VARCHAR".equals(type)) {
                    ifStartBelan = format("\t\t\t<if test=\"%s != null and %s != '' \">\n", cName, cName);
                } else {
                    ifStartBelan = format("\t\t\t<if test=\"%s != null \">\n", cName);
                }
                String andString = format("\t\t\t\tand %s = #{%s} \n", columnName, cName);
                String ifEndBelan = "			</if>\n";

                ifLabel.append(ifStartBelan);
                ifLabel.append(andString);
                ifLabel.append(ifEndBelan);

                if (!columnName.equals("id")) {
                    updateSetLabelSb.append(ifStartBelan);
                    updateSetLabelSb.append(format("\t\t\t\t%s = #{%s},  \n", columnName, cName));
                    updateSetLabelSb.append(ifEndBelan);
                }
            }
            StringBuilder xmlFileSb = new StringBuilder();

            xmlFileSb.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n");
            xmlFileSb.append("<!DOCTYPE mapper PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\" \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\" >\n");
            xmlFileSb.append("<mapper namespace=\"___DPath___._____X______DAO\">\n\n");
            xmlFileSb.append("	<select id=\"get\" parameterType=\"___EPath___._____X______Entity\" resultType=\"___EPath___._____X______Entity\">\n");
            xmlFileSb.append("		SELECT \n");
            xmlFileSb.append(asColumn);
            xmlFileSb.append("		FROM _____TableName______ \n");
            xmlFileSb.append("		<where>\n");
            xmlFileSb.append(ifLabel);
            xmlFileSb.append("		</where>\n");
            xmlFileSb.append("	</select>\n\n");
            xmlFileSb.append("	<select id=\"getById\" parameterType=\"java.lang.Integer\" resultType=\"___EPath___._____X______Entity\">\n");
            xmlFileSb.append("		SELECT \n");
            xmlFileSb.append(asColumn);
            xmlFileSb.append("		FROM _____TableName______ \n");
            xmlFileSb.append("		<where>\n");
            xmlFileSb.append("          _____TableName______.id = #{0}\n");
            xmlFileSb.append("		</where>\n");
            xmlFileSb.append("	</select>\n\n");
            xmlFileSb.append("	<insert id=\"add\"  parameterType=\"___EPath___._____X______Entity\" useGeneratedKeys=\"true\" keyProperty=\"id\">\n");
            xmlFileSb.append("		INSERT INTO _____TableName______ (\n");
            xmlFileSb.append(addColumnUp);
            xmlFileSb.append(addColumnDown);
            xmlFileSb.append("	</insert>\n\n");
            xmlFileSb.append("	<delete id=\"delete\" parameterType=\"___EPath___._____X______Entity\">\n");
            xmlFileSb.append("		DELETE FROM _____TableName______\n");
            xmlFileSb.append("		<where>\n");
            xmlFileSb.append(ifLabel);
            xmlFileSb.append("		</where>\n");
            xmlFileSb.append("	</delete>\n\n");
            xmlFileSb.append("	<update id=\"update\" parameterType=\"___EPath___._____X______Entity\">\n");
            xmlFileSb.append("		UPDATE _____TableName______\n");
            xmlFileSb.append("		<set>\n");
            xmlFileSb.append(updateSetLabelSb);
            xmlFileSb.append("		</set>\n");
            xmlFileSb.append("		<where>\n");
            xmlFileSb.append(ifLabel);
            xmlFileSb.append("		</where>\n");
            xmlFileSb.append("	</update>\n\n");
            xmlFileSb.append("	<select id=\"selectList\" parameterType=\"java.util.Map\" resultType=\"___EPath___._____X______Entity\">\n");
            xmlFileSb.append("		SELECT \n");
            xmlFileSb.append(asColumn);
            xmlFileSb.append("		FROM _____TableName______ \n");
            xmlFileSb.append("		<where>\n");
            xmlFileSb.append(ifLabel);
            xmlFileSb.append("		</where>\n");
            xmlFileSb.append("		<if test=\"pageIndex != null \">\n");
            xmlFileSb.append("			<if test=\"pageNumber != null \">\n");
            xmlFileSb.append("				LIMIT #{pageIndex}, #{pageNumber}\n");
            xmlFileSb.append("			</if>\n");
            xmlFileSb.append("		</if>\n");
            xmlFileSb.append("	</select>\n");
            xmlFileSb.append("</mapper>");

            String xmlCode = xmlFileSb.toString();

            xmlCode = extracted(voName, className, xmlCode);

            xmlCode = xmlCode.replace("_____TableName______", tableName);

            outFile(xmlCode, xml_Path, className + "XML", "xml");
        }
    }

    /**
     * 创建实体类
     *
     * @param tableNameMaplist
     */
    @SuppressWarnings("unchecked")
    private void createVo(List<Object> tableNameMaplist) {
        for (Object object : tableNameMaplist) {
            Map<String, Object> tableMap = (Map<String, Object>) object;

            String tableName = (String) tableMap.get("tableName");
            String tableRemarks = (String) tableMap.get("remarks");
            // 转为驼峰
            String voName = nameTransformation(tableName);
            // 首字母大写
            String className = titleCase(voName) + "Entity";

            StringBuilder voClassFileSb = new StringBuilder();

            StringBuilder getVo = new StringBuilder();
            getVo.append(format("\tpublic %s(Map<String, Object> map) {\n", className));
            getVo.append("		if (map == null || map.isEmpty()) {\n");
            getVo.append("			return;\n");
            getVo.append("		}\n\n");


            voClassFileSb.append(format("package %s;\n\n", getPackagePath(vo_Path)));
            voClassFileSb.append("import java.util.Date;\n");
            voClassFileSb.append("import java.math.BigDecimal;\n");
            voClassFileSb.append("import java.util.Map;\n");
            voClassFileSb.append("import java.util.HashMap;\n");
            voClassFileSb.append("import com.alibaba.fastjson.JSONObject;\n");
            voClassFileSb.append("import java.sql.Timestamp;\n");
            voClassFileSb.append("import lombok.Data;\n");
            voClassFileSb.append("import java.io.Serializable;\n\n");

            voClassFileSb.append("@Data\n");
            voClassFileSb.append(format("public class %s implements Serializable {\n\n", className));

            long nextLong = new Random(System.currentTimeMillis()).nextLong();

            voClassFileSb.append("	/**\n");
            voClassFileSb.append("	 * 版本号\n");
            voClassFileSb.append(format("\t * 表备注：%s\n", tableRemarks));
            voClassFileSb.append("	 */\n");
            voClassFileSb.append(format("\tprivate static final long serialVersionUID = %dL;\n\n", nextLong));


            StringBuilder voToMap = new StringBuilder();
            voToMap.append("    public Map toMap() {");
            voToMap.append("\n        Map map = new HashMap();");


            List<Object> list = (List<Object>) tableMap.get("columnList");
            for (int i = 0; i < list.size(); i++) {
                Map<String, Object> columnMap = (Map<String, Object>) list.get(i);

                String columnName = (String) columnMap.get("columnName");
                // 字段名转为驼峰
                String cName = nameTransformation(columnName);

                String remarks = (String) columnMap.get("remarks");

                // 转为对应的java类型
                String javaType = typeTransformation((String) columnMap.get("type"));

                voClassFileSb.append(format("\t/**\n\t *%s\n\t */\n", remarks));
                voClassFileSb.append(format("\tprivate %s %s;\n\n", javaType, cName));

                if ("String".equals(javaType)) {
                    getVo.append(format("\t\t%s = (String) map.get(\"%s\");\n\n", cName, cName));
                } else if ("Date".equals(javaType)) {
                    getVo.append(format("\t\tObject %sobj = map.get(\"%s\");\n", cName, cName));
                    getVo.append(format("\t\tif (%sobj != null) {\n", cName));
                    getVo.append(format("\t\t\tif (%sobj instanceof Date) {\n", cName));
                    getVo.append(format("\t\t\t\t%s = (Date) %sobj;\n", cName, cName));
                    getVo.append("			} else {\n");
                    getVo.append(format("\t\t\t\t%s = new Date((long) %sobj);\n", cName, cName));
                    getVo.append("			}\n");
                    getVo.append("		}\n\n");
                } else if ("BigDecimal".equals(javaType)) {
                    getVo.append(format("\t\tif (map.get(\"%s\") != null) {\n", cName));
                    getVo.append(format("\t\t\t%s = new BigDecimal(String.valueOf(map.get(\"%s\")));\n", cName, cName));
                    getVo.append("		}\n\n");
                } else {
                    getVo.append(format("\t\tif (map.get(\"%s\") != null) {\n", cName));
                    getVo.append(format("\t\t\t%s = %s.valueOf(String.valueOf(map.get(\"%s\")));\n", cName, javaType, cName));
                    getVo.append("		}\n\n");
                }

                voToMap.append(format("\n        map.put(\"%s\", %s);", cName, cName));
            }
            getVo.append("	}\n\n");

            voToMap.append("\n        return map;");
            voToMap.append("\n    }\n\n");

            voClassFileSb.append("	@SuppressWarnings(\"unchecked\")\n");
            voClassFileSb.append(format("\tpublic %s(String json) {\n", className));
            voClassFileSb.append("		this(JSONObject.parseObject(json, Map.class));\n");
            voClassFileSb.append("	}\n\n");
            voClassFileSb.append(format("\tpublic %s() {}\n\n", className));
            voClassFileSb.append(getVo);
            voClassFileSb.append(voToMap);

            voClassFileSb.append("\n}");

            outFile(voClassFileSb.toString(), vo_Path, className, "java");
        }
    }

    private String typeTransformation(String type) {
        if (type.equals("INT") || type.equals("TINYINT") || type.equals("SMALLINT") || type.equals("INT UNSIGNED")) {
            return "Integer";
        } else if (type.equals("VARCHAR") || type.equals("TEXT") || type.equals("LONGTEXT") || type.equals("JSON")) {
            return "String";
        } else if (type.equals("BIGINT")) {
            return "Long";
        } else if (type.equals("BIT")) {
            return "Boolean";
        } else if (type.equals("FLOAT")) {
            return "Float";
        } else if (type.equals("DOUBLE")) {
            return "Double";
        } else if (type.equals("TIMESTAMP")) {
            return "Timestamp";
        } else if (type.equals("DECIMAL") || type.equals("DECIMAL UNSIGNED")) {
            return "BigDecimal";
        } else if (type.equals("DATETIME") || type.equals("DATE")) {
            return "Date";
        }
        return type;
    }

    /**
     * 输出文件到指定目录文件夹
     *
     * @param code
     * @param path
     * @param fileName
     * @param fileType
     */
    private void outFile(String code, String path, String fileName, String fileType) {
        try {
            File file = new File(path + "//" + fileName + "." + fileType);

            if (!file.exists()) {
                new File(path).mkdirs();
                file.createNewFile();
            }
            OutputStreamWriter write = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");
            BufferedWriter writer = new BufferedWriter(write);
            writer.write(code);
            writer.flush();
            writer.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private String nameTransformation(String name) {
        if (name.indexOf("T_") == 0 || name.indexOf("t_") == 0) {
            name = name.substring(2, name.length());
        }

        Pattern linePattern = Pattern.compile("_(\\w)");
        name = name.toLowerCase();
        Matcher matcher = linePattern.matcher(name);
        StringBuffer sb = new StringBuffer();
        while (matcher.find()) {
            matcher.appendReplacement(sb, matcher.group(1).toUpperCase());
        }
        matcher.appendTail(sb);
        String string = sb.toString();

        return string;

    }

    /**
     * 首字母大写
     *
     * @param string
     * @return
     */
    private String titleCase(String string) {
        return string.substring(0, 1).toUpperCase() + string.substring(1, string.length());
    }
}
